import math
import os
import time
import sys
from itertools import groupby
from operator import itemgetter

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class SyncSecuritiesFinData:
    """
    智慧金融-证券公司 数据导入
    """

    def __init__(self, logger, localDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb

    def __loadLocalData__(self):
        sql = """
            SELECT * FROM security_fin_data 
        """
        return self.localDb.Query(sql)

    def __loadFinProSeCompare__(self):
        sql = """
            SELECT COMPCODE, REPORTYEAR, REPORTDATETYPE, NCAP,NA,
            NCAPTONA,NCAPTODEBT,NATODEBT,NCAPTOSUMRISKRES,CALERATIO,LSILCRTO,
            NESTFURATE,EQYSECPROTONCAP,EQNSECPROTONCAP
            FROM tq_fin_prosecompare
            WHERE ISVALID = 1
        """
        return self.fcdb.Query(sql)

    def __update__(self, updateDatas):
        sql = """
            REPLACE INTO security_fin_data
            (ID, COMPCODE, REPORTYEAR, REPORTDATETYPE, NCAP, NA, NCAPTONA, 
            NCAPTODEBT, NATODEBT,NCAPTOSUMRISKRES, CALERATIO, LSILCRTO, NESTFURATE, 
            EQYSECPROTONCAP, EQNSECPROTONCAP)
            VALUES(%(ID)s,%(COMPCODE)s,%(REPORTYEAR)s,%(REPORTDATETYPE)s,%(NCAP)s,%(NA)s,%(NCAPTONA)s,
            %(NCAPTODEBT)s,%(NATODEBT)s,%(NCAPTOSUMRISKRES)s,%(CALERATIO)s,%(LSILCRTO)s,%(NESTFURATE)s,
            %(EQYSECPROTONCAP)s,%(EQNSECPROTONCAP)s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def sync(self):

        self.logger.info("加载本地数据")
        localDatas = self.__loadLocalData__()
        localDatas = {self.__genKey__(d): d for d in localDatas}
        self.logger.info(f"加载本地数据 {len(localDatas)}")

        self.logger.info("加载证券专项对比")
        finProSeCompareDatas = self.__loadFinProSeCompare__()
        self.logger.info(f"加载证券专项对比 {len(finProSeCompareDatas)}")

        updatedCount = 0
        insertedCount = 0
        for finProSeCompareData in finProSeCompareDatas:
            key = self.__genKey__(finProSeCompareData)
            if key in localDatas.keys():
                data = localDatas[key]
                data.update(finProSeCompareData)
                updatedCount += 1
            else:
                data = self.__genEmptyData__()
                data.update(finProSeCompareData)
                localDatas[key] = data
                insertedCount += 1

        self.logger.info(f"证券专项对比 新增：{insertedCount},更新：{updatedCount}")

        # 写库
        updateDatas = localDatas.values()
        self.__update__(updateDatas)
        self.logger.info(f"本地数据更新 更新：{len(updateDatas)}")

    @staticmethod
    def __genKey__(data: dict):
        return data["COMPCODE"], data["REPORTYEAR"], data["REPORTDATETYPE"]

    @staticmethod
    def __convertReportDate__(reportDate):
        reportYear = None
        reportDateType = None
        if reportDate is None:
            return reportYear, reportDateType
        reportYear = reportDate[:4]
        md = reportDate[4:]
        if md == "0331":
            return reportYear, "1"
        if md == "0630":
            return reportYear, "2"
        if md == "0930":
            return reportYear, "3"
        if md == "1231":
            return reportYear, "4"

        return reportYear, reportDateType

    # 空数据
    @staticmethod
    def __genEmptyData__():
        return {
            "ID": None,
            "COMPCODE": None,
            "REPORTYEAR": None,
            "REPORTDATETYPE": None,
            "NCAP": None,
            "NA": None,
            "NCAPTONA": None,
            "NCAPTODEBT": None,
            "NCAPTOSUMRISKRES": None,
            "CALERATIO": None,
            "LSILCRTO": None,
            "NESTFURATE": None,
            "EQYSECPROTONCAP": None,
            "EQNSECPROTONCAP": None
        }

    @staticmethod
    def __getDictData__(datas: dict, key):
        if datas is None or key is None:
            return None
        if key in datas.keys():
            return datas[key]
        return None


# 主函数
def main():
    logger = Logger()
    cleanScreen()

    localDb = ProdDb(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    t = time.perf_counter()

    logger.info("========同步智慧金融-证券公司 开始 ========")

    sync = SyncSecuritiesFinData(logger=logger, localDb=localDb, fcdb=fcdb)
    sync.sync()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========同步智慧金融-证券公司 结束 ========")


# 程序入口
if __name__ == "__main__":
    main()
